"""
MySQL版本的数据库工具模块
替代原有的SQLite版本的database_utils_stable04_01.py
"""

from dbutils.pooled_db import PooledDB
import pymysql
from PyQt5.QtWidgets import QMessageBox

# 创建数据库连接池
db_pool = PooledDB(
    creator=pymysql,
    host='172.28.196.123',  # MySQL服务器地址
    user='remote_admin',    # MySQL用户名
    password='alibaba999',  # MySQL密码
    database='trade_monitor', # MySQL数据库名
    port=3306,              # MySQL默认端口
    maxconnections=5,       # 最大连接数
    mincached=2,            # 初始化时创建的空闲连接数
    maxcached=3,            # 连接池最大空闲连接数
    maxshared=3,            # 共享连接的最大数量
    blocking=True,          # 连接池中如果没有可用连接后是否阻塞等待
    autocommit=True,        # 自动提交
    charset='utf8mb4'       # 字符集
)

def connect_db():
    """
    获取数据库连接
    :return: 数据库连接对象，如果连接失败则返回None
    """
    try:
        conn = db_pool.connection()
        return conn
    except Exception as e:
        print(f"数据库连接失败: {e}")
        QMessageBox.critical(None, "错误", f"数据库连接失败: {e}")
        return None

# 交易品种管理函数
def get_all_products(conn):
    """
    获取所有产品信息
    :param conn: 数据库连接
    :return: 所有产品记录列表
    """
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM products ORDER BY is_monitored DESC")
    result = cursor.fetchall()
    cursor.close()
    return result

def insert_product(conn, data):
    """
    插入新产品
    :param conn: 数据库连接
    :param data: 产品数据元组 (name, exchange_code, margin, stoploss, takeprofit, limit_stoploss, mtp, dks, is_monitored)
    """
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO products (name, exchange_code, margin, stoploss, takeprofit, limit_stoploss, mtp, dks, is_monitored) "
        "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)", 
        data
    )
    conn.commit()
    cursor.close()

def update_product(conn, product_id, data):
    """
    更新产品信息
    :param conn: 数据库连接
    :param product_id: 产品ID
    :param data: 要更新的字段字典 {字段名: 新值}
    """
    update_query = "UPDATE products SET "
    update_query += ", ".join([f"{key} = %s" for key in data.keys()])
    update_query += " WHERE id = %s"
    values = list(data.values()) + [product_id]
    cursor = conn.cursor()
    cursor.execute(update_query, values)
    conn.commit()
    cursor.close()

def delete_product(conn, product_id):
    """
    删除产品
    :param conn: 数据库连接
    :param product_id: 产品ID
    """
    cursor = conn.cursor()
    cursor.execute("DELETE FROM products WHERE id = %s", (product_id,))
    conn.commit()
    cursor.close()

def get_monitored_products(conn):
    """
    获取所有被监控的产品
    :param conn: 数据库连接
    :return: 被监控的产品记录列表
    """
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM products WHERE is_monitored = 1")
    result = cursor.fetchall()
    cursor.close()
    return result

def delete_product_by_name(conn, product_name):
    """
    根据名称删除产品
    :param conn: 数据库连接
    :param product_name: 产品名称
    """
    cursor = conn.cursor()
    cursor.execute("DELETE FROM products WHERE name = %s", (product_name,))
    conn.commit()
    cursor.close()

def get_product_by_id(conn, product_id):
    """
    根据ID获取产品
    :param conn: 数据库连接
    :param product_id: 产品ID
    :return: 产品记录
    """
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM products WHERE id = %s", (product_id,))
    result = cursor.fetchone()
    cursor.close()
    return result

# 策略管理页面需要用到的数据库操作函数
def get_all_strategies(conn):
    """
    获取所有策略
    :param conn: 数据库连接
    :return: 所有策略记录列表
    """
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM products")
    result = cursor.fetchall()
    cursor.close()
    return result

def update_strategy(conn, strategy_id, updates):
    """
    更新策略数据
    :param conn: 数据库连接
    :param strategy_id: 策略ID
    :param updates: 要更新的字段字典 {字段名: 新值}
    """
    if not conn:
        raise ValueError("数据库连接无效")
    if not strategy_id:
        raise ValueError("策略ID不能为空")
    
    columns = list(updates.keys())  # 使用传入的键作为列名
    set_clause = ", ".join([f"{col} = %s" for col in columns])
    values = list(updates.values()) + [strategy_id]
    
    cursor = conn.cursor()
    try:
        # 验证策略是否存在
        cursor.execute("SELECT id FROM products WHERE id = %s", (strategy_id,))
        if not cursor.fetchone():
            raise ValueError(f"策略ID {strategy_id} 不存在")
            
        # 执行更新
        cursor.execute(f"UPDATE products SET {set_clause} WHERE id = %s", values)
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()

# 交易记录管理
def insert_trade_record(conn, record):
    """
    添加交易记录
    :param conn: 数据库连接
    :param record: 交易记录元组 (product_name, exchange_code, direction, action_type, volume, price, remark)
    :return: 新记录的ID
    """
    sql = '''INSERT INTO trade_records(
                product_name, exchange_code,
                direction, action_type, volume, 
                price, remark)
             VALUES(%s, %s, %s, %s, %s, %s, %s)'''
    cursor = conn.cursor()
    try:
        cursor.execute(sql, record)
        conn.commit()
        last_id = cursor.lastrowid
        cursor.close()
        return last_id
    except Exception as e:
        print(f"添加交易记录错误: {e}")
        return None

def get_trade_records(conn, product_name=None, limit=100):
    """
    获取交易记录
    :param conn: 数据库连接
    :param product_name: 产品名称（可选）
    :param limit: 返回记录数量限制
    :return: 交易记录列表
    """
    cursor = conn.cursor()
    if product_name:
        cursor.execute(
            "SELECT * FROM trade_records WHERE product_name = %s ORDER BY timestamp DESC LIMIT %s", 
            (product_name, limit)
        )
    else:
        cursor.execute("SELECT * FROM trade_records ORDER BY timestamp DESC LIMIT %s", (limit,))
    
    result = cursor.fetchall()
    cursor.close()
    return result

# 配置管理
def get_config(conn, key):
    """
    获取配置项
    :param conn: 数据库连接
    :param key: 配置键
    :return: 配置值
    """
    cursor = conn.cursor()
    cursor.execute("SELECT value FROM config WHERE `key` = %s", (key,))
    result = cursor.fetchone()
    cursor.close()
    return result[0] if result else None

def set_config(conn, key, value):
    """
    设置配置项
    :param conn: 数据库连接
    :param key: 配置键
    :param value: 配置值
    """
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO config (`key`, value) VALUES (%s, %s) ON DUPLICATE KEY UPDATE value = %s", 
        (key, value, value)
    )
    conn.commit()
    cursor.close()

def get_all_configs(conn):
    """
    获取所有配置项
    :param conn: 数据库连接
    :return: 配置字典 {键: 值}
    """
    cursor = conn.cursor()
    cursor.execute("SELECT `key`, value FROM config")
    result = cursor.fetchall()
    cursor.close()
    return {row[0]: row[1] for row in result}

# 测试数据库连接
if __name__ == "__main__":
    try:
        conn = connect_db()
        if conn:
            print("数据库连接成功!")
            # 测试查询
            cursor = conn.cursor()
            cursor.execute("SELECT 1")
            print("查询测试成功:", cursor.fetchone())
            cursor.close()
            conn.close()
        else:
            print("数据库连接失败!")
    except Exception as e:
        print(f"测试过程中出错: {e}")